Attribute VB_Name = "AffAcctList"
'Written by Jalisa Greene
'Last Modified 6/10/2014
'Modified by Jalisa Greene


Sub GenAffectedAcctList()
    Dim rowNum As Integer
    Dim aCell As Range
    Dim lastRow As Integer
    
    Application.ScreenUpdating = False
    Worksheets("Affected Account List ").Activate
    lastRow = Range("A500").End(xlUp).Row
    
    For Each aCell In Range("A9", "A" & lastRow)
        '**FOR EACH CELL, IF NOT EMPTY PULL DBA, AND ADDRESS FROM INVENTORY TAB'
        aCell.Select
        If aCell.Value <> "" Then
            rowNum = WorksheetFunction.Match(aCell.Value, Worksheets("INV").Range("locations"), 0) + 7
            ActiveCell.Offset(, 1).Value = Worksheets("INV").Range("B" & rowNum).Value     '**DBA
            ActiveCell.Offset(, 3).Value = Worksheets("INV").Range("C" & rowNum).Value    '**ADDR
            ActiveCell.Offset(, 4).Value = Worksheets("INV").Range("D" & rowNum).Value    '**CITY
            ActiveCell.Offset(, 5).Value = Worksheets("INV").Range("E" & rowNum).Value     '**STATE
            ActiveCell.Offset(, 6).Value = Worksheets("INV").Range("F" & rowNum).Value     '**ZIP
            
            '**CSG SECTION
            Range("H" & ActiveCell.Row).Select
            If Worksheets("ASR").Range("AA" & rowNum).Value <> "" Then
                '**WE HAVE CONTAINER TYPE 1
                ActiveCell.Value = Worksheets("ASR").Range("AA" & rowNum).Value
                ActiveCell.Offset(, 1).Value = Worksheets("ASR").Range("AB" & rowNum).Value    '**Acct num
                ActiveCell.Offset(, 3).Value = Worksheets("ASR").Range("AN" & rowNum).Value    '**Billing Freq
                If Len(Worksheets("ASR").Range("BE" & rowNum).Value) > 15 Then                        '**must match contract for cs phone, & rep info'
                    contract = Worksheets("ASR").Range("BE" & rowNum).Value
                    contractRow = WorksheetFunction.Match(contract, Worksheets("Contract Data").Range("C10:C100"), 0) + 9
                    ActiveCell.Offset(, 4).Value = Worksheets("Contract Data").Range("AH" & contractRow).Value    '**CS Phone #
                    ActiveCell.Offset(, 5).Value = Worksheets("Contract Data").Range("V" & contractRow).Value       '**Rep Name
                    ActiveCell.Offset(, 6).Value = Worksheets("Contract Data").Range("W" & contractRow).Value       '**Rep Number
                End If
                    
                If Worksheets("ASR").Range("CA" & rowNum).Value <> "" And Worksheets("ASR").Range("CA" & rowNum).Value <> Worksheets("ASR").Range("AA" & rowNum).Value Then
                    '**WE HAVE CONTAINER TYPE 2
                    ActiveCell.Offset(1).EntireRow.Insert
                    With ActiveCell.Offset(1)
                        .Select
                        .Value = Worksheets("ASR").Range("CA" & rowNum).Value
                    End With
                    ActiveCell.Offset(, 1).Value = Worksheets("ASR").Range("CB" & rowNum).Value      '**Acct num
                    ActiveCell.Offset(, 3).Value = Worksheets("ASR").Range("CM" & rowNum).Value     '**Billing Freq
                    If Len(Worksheets("ASR").Range("DE" & rowNum).Value) > 15 Then                        '**must match contract for cs phone, & rep info'
                        contract = Worksheets("ASR").Range("DE" & rowNum).Value
                        contractRow = WorksheetFunction.Match(contract, Worksheets("Contract Data").Range("C10:C100"), 0) + 9
                        ActiveCell.Offset(, 4).Value = Worksheets("Contract Data").Range("AH" & contractRow).Value      '**CS Phone #
                        ActiveCell.Offset(, 5).Value = Worksheets("Contract Data").Range("V" & contractRow).Value        '**Rep Name
                        ActiveCell.Offset(, 6).Value = Worksheets("Contract Data").Range("W" & contractRow).Value       '**Rep Number
                    End If
                    
                    If Worksheets("ASR").Range("EA" & rowNum).Value <> "" And Worksheets("ASR").Range("EA" & rowNum).Value <> Worksheets("ASR").Range("AA" & rowNum).Value Then
                        '**WE HAVE CONTAINER TYPE 3
                        ActiveCell.Offset(1).EntireRow.Insert
                        With ActiveCell.Offset(1)
                            .Select
                            .Value = Worksheets("ASR").Range("EA" & rowNum).Value
                        End With
                        ActiveCell.Offset(, 1).Value = Worksheets("ASR").Range("EB" & rowNum).Value         '**Acct num
                        ActiveCell.Offset(, 3).Value = Worksheets("ASR").Range("EM" & rowNum).Value        '**Billing Freq
                        If Len(Worksheets("ASR").Range("FE" & rowNum).Value) > 15 Then                            '**must match contract for cs phone, & rep info'
                            contract = Worksheets("ASR").Range("FE" & rowNum).Value
                            contractRow = WorksheetFunction.Match(contract, Worksheets("Contract Data").Range("C10:C100"), 0) + 9
                            ActiveCell.Offset(, 4).Value = Worksheets("Contract Data").Range("AH" & contractRow).Value      '**CS Phone #
                            ActiveCell.Offset(, 5).Value = Worksheets("Contract Data").Range("V" & contractRow).Value         '**Rep Name
                            ActiveCell.Offset(, 6).Value = Worksheets("Contract Data").Range("W" & contractRow).Value         '**Rep Number
                        End If
                        
                        
                        If Worksheets("ASR").Range("GA" & rowNum).Value <> "" And Worksheets("ASR").Range("GA" & rowNum).Value <> Worksheets("ASR").Range("AA" & rowNum).Value Then
                            '**WE HAVE CONTAINER TYPE 4
                            ActiveCell.Offset(1).EntireRow.Insert
                            With ActiveCell.Offset(1)
                                .Select
                                .Value = Worksheets("ASR").Range("GA" & rowNum).Value
                            End With
                            ActiveCell.Offset(, 1).Value = Worksheets("ASR").Range("GB" & rowNum).Value          '**Acct num
                            ActiveCell.Offset(, 3).Value = Worksheets("ASR").Range("GM" & rowNum).Value         '**Billing Freq
                            If Len(Worksheets("ASR").Range("HE" & rowNum).Value) > 15 Then                             '**must match contract for cs phone, & rep info'
                                contract = Worksheets("ASR").Range("HE" & rowNum).Value
                                contractRow = WorksheetFunction.Match(contract, Worksheets("Contract Data").Range("C10:C100"), 0) + 9
                                ActiveCell.Offset(, 4).Value = Worksheets("Contract Data").Range("AH" & contractRow).Value         '**CS Phone #
                                ActiveCell.Offset(, 5).Value = Worksheets("Contract Data").Range("V" & contractRow).Value           '**Rep Name
                                ActiveCell.Offset(, 6).Value = Worksheets("Contract Data").Range("W" & contractRow).Value           '**Rep Number
                            End If
                            
                            If Worksheets("ASR").Range("IA" & rowNum).Value <> "" And Worksheets("ASR").Range("IA" & rowNum).Value <> Worksheets("ASR").Range("AA" & rowNum).Value Then
                                '**WE HAVE CONTAINER 5
                                ActiveCell.Offset(1).EntireRow.Insert
                                With ActiveCell.Offset(1)
                                    .Select
                                    .Value = Worksheets("ASR").Range("IA" & rowNum).Value
                                End With
                                ActiveCell.Offset(, 1).Value = Worksheets("ASR").Range("IB" & rowNum).Value         '**Acct num
                                ActiveCell.Offset(, 3).Value = Worksheets("ASR").Range("IM" & rowNum).Value        '**Billing Freq
                                If Len(Worksheets("ASR").Range("JE" & rowNum).Value) > 15 Then                            '**must match contract for cs phone, & rep info'
                                    contract = Worksheets("ASR").Range("JE" & rowNum).Value
                                    contractRow = WorksheetFunction.Match(contract, Worksheets("Contract Data").Range("C10:C100"), 0) + 9
                                    ActiveCell.Offset(, 4).Value = Worksheets("Contract Data").Range("AH" & contractRow).Value          '**CS Phone #
                                    ActiveCell.Offset(, 5).Value = Worksheets("Contract Data").Range("V" & contractRow).Value             '**Rep Name
                                    ActiveCell.Offset(, 6).Value = Worksheets("Contract Data").Range("W" & contractRow).Value            '**Rep Number
                                End If
                                
                            End If '**ends Container 5
                        End If '**ends container 4
                    End If '**ends container 3
                End If  '**ends container 2
            ElseIf Worksheets("ASR").Range("KA" & rowNum).Value <> "" Then
            'we have no regular containers, only compactors'
                ActiveCell.Value = Worksheets("ASR").Range("KA" & rowNum).Value
            End If
        End If
    Next
        

        
End Sub
